Axis
functions
Axis function is:
· Function that is always applied on the whole data set on a single axis (rows or columns),
Once applied, you can add new elements
(levels or members) from already existing dimension
inside axis or elements from new dimensions to
the axis.
Those new elements will be added, always
inside function.
List of functions that are supported in designer:
|
CubePlayer
Functions |
MDX
Functions used to create CubePlayer
Functions |
|
Best
N |
Equivalent to TopCount |
|
Best in
Sum |
Equivalent to TopSum |
|
Best in
Percent |
Equivalent to TopPercent |
|
Worst
N |
Equivalent to BottomCount |
|
Worst in
Sum |
Equivalent to BottomSun |
|
Worst in
Percent |
Equivalent to BottomPercent |
|
Order |
Equivalent to
Order |
|
Order by
Alphabet |
Combination of Order
function and order conditions |
|
Order by
Hierarchy |
Equivalent to Hierachize |
|
Filter |
Equivalent to
Filter |
|
Remove
member(s) |
Combination of Filter
(and NOT members …) |
|
First
N |
Equivalent to
Head |
|
Last
N |
Equivalent to
Tail |
|
Contribution |
Special sintax |
|
For each from previous
dimension |
Equivalent to
Generate |
|
Without
empties |
Equivalent to NonEmpty |
Applying the Axis
(External) functions
1.
Fill
the row or column panel with cube elements:
Figure 6-2: The member
USA is in the Rows (With member function Children applied), and level Year is in
the Columns
2.
Click
the green icon in the Rows Panel title bar
or
Right
click the element USA -> Axis Functions
3.
Select
function from the menu
Figure 6-3: Clicking the
green function button opens Axis functions menu
4.
The
function dialog will open
Figure
6-4: The dialog for setting parameters of the Best N
function
-
Select Measure to apply the function from drop
down list (a default measure will be predefined)
-
Select Value (if
applicable)
-
Click Create
5.
Thus created function will be applied to set (row set in this case) regardless of
how much you change elements inside row axis. It will be applied until you remove it
from designer or until axis is
cleared. The green icon will turn red, indicating that Axis function is
applied to Rows axis.
Figure 6-5: The function Best N is applied to Rows, indicated by
the red function icon.
Note: Whenever you save
analysis, the function will be saved as well.
Example:
Axis functions
Axis functions are always applied to entire axis regardless of number of dimension-hierarchies at that axis.
Example
We have level
We will apply TopCount 100 on entire axis
SELECT
NON
EMPTY
TopCount
(
{
[Customer].[hCountry].[
}
,100,
[Measures].[Store Sales]
)
ON AXIS(0)
FROM
[Sales]
Now when we add another dimension/hierarch, let us say Product Family it will come inside function or better to say
function will be applied after CrossJoin:
SELECT
NON
EMPTY
TopCount
(
CrossJoin
(
{
[Customer].[hCountry].[
},
{
[Product].[hProduct].[Product
Family].MEMBERS
}
)
,100,
[Measures].[Store Sales]
)
ON AXIS(0)
FROM
[Sales]
The same will be when we add third dimension:
SELECT
NON
EMPTY
TopCount
(
CrossJoin
(
CrossJoin
(
{
[Customer].[hCountry].[
},
{
[Product].[hProduct].[Product
Family].MEMBERS
}
),
{
[Store].[hStoreType].[Store
Type].MEMBERS
}
)
,100,
[Measures].[Store Sales]
)
ON AXIS(0)
FROM
[Sales]
Editing and removing
Axis (External) functions
Whenever an Axis
function is applied, on the bottom part of the context menu, you will find
options Edit function and Remove function (See Figure 6-6). You will be able to
select function to edit / remove from the submenu to the
left.
Editing the function will open
the dialog that was used to create specific type of the
function.
Removing the function will
remove it without confirmation. However, if you removed the function by
accident, you can apply it back by using the button Back from the main
toolbar.
Figure 6-6: Additional
options in the context menu, when function(s) are applied.